File manager

File manager - Edit - /home/autoph/public_html/data03252025consolidation/include/components/sms_service/request_data.php

Back
<?php // include('api/controllers/utility.php'); // $utility = new Utility(); $return_array = array(); $request_session_data = array(); if(isset($_POST['request_id']) \|\| isset($_REQUEST['request-id'])){ //request id if(isset($_POST['request_id'])){ $request_session_data['request_id'] = $db -> escape($_POST['request_id']); } if(isset($_REQUEST['request-id'])){ $request_session_data['request_id'] = $db -> escape($_REQUEST['request-id']); } //echo "request_data: " . $_REQUEST['request-id']; return; //$request_session_data['request_id'] = $db -> escape($_REQUEST['request-id']); $current_user = $_SESSION['user']['id']; $check_request_query = "SELECT ( IF((SELECT count(id) FROM `request_status` WHERE request_id = tb.id AND `status` = 2) = 0, IF((SELECT count(id) FROM `request_status` WHERE request_id = tb.id AND `status` = 0) = 0, '1' , '0' ) , '2' ) ) as status FROM `text_blast` tb INNER JOIN request_type rt ON tb.request_type_id = rt.id WHERE tb.id = '" . $request_session_data['request_id'] . "' AND tb.employee_id = '$current_user'"; //echo $check_request_query; return; $check_request = $db -> select($check_request_query); // IF APPROVE OR NOT // if(!isNotEmpty($check_request) \|\| intval($check_request) <> 1){ // //echo "Hi"; // header('location: requested_sms_service.php'); // return; // } $fmt = new NumberFormatter('PHP', NumberFormatter::CURRENCY); //START GET LIST OF APPROVER $request_session_data['approver_list'] = array(); $approver_query = "SELECT e.employee_id,concat(e.first_name,' ',e.last_name) as name, rs.`status`, DATE_FORMAT(date,'%M %d, %Y - %h:%i %p') as date, rs.`notes`, rs.status FROM `request_status` rs INNER JOIN employee e ON rs.employee_id = e.id WHERE rs.request_id = '" . $request_session_data['request_id'] . "' ORDER BY rs.id ASC"; // "Approver Query: " . $approver_query; return; $request_session_data['request_status'] = 0; $list_query = $db -> sql_query($approver_query); $check_pending = "SELECT COUNT() FROM `request_status` WHERE `request_id` = '" . $request_session_data['request_id'] . "' AND `status` = 0"; $count_pending = $db->select($check_pending); while($row = $list_query->fetch_assoc()) { // $approver_table = $approver_table . "<tr><th class="."data mr-3".">".$row["name"]."&nbsp&nbsp-&nbsp </th> <th class="."data"." >".$row["date"]."</th></tr>"; $request_session_data['approver_list'][] = array($row["employee_id"], $row["name"], $row["date"],$row["notes"] ); $request_session_data['request_status'] = $row['status']; } //END GET LIST OF APPROVER $parameter_query = "SELECT tb.`id`, tb.`control_number`, CONCAT(e.first_name,' ',e.middle_name,' ',e.last_name) as request_by, CONCAT(DATE_FORMAT(tb.`request_date`, '%M %d, %Y'), ' - ' ,TIME_FORMAT(tb.`request_time`, '%h:%i %p')) as request_date_time, rt.name as request_type, tb.request_type_id, tb.`dms_id`, IF( tb.`dms_id` = '0','All', (SELECT name FROM source_dms WHERE id = tb.dms_id) ) as dms_name, tb.`company_id`, IF( tb.`company_id` = '0','All', (SELECT code FROM source_company WHERE id = tb.company_id) ) as company_name, tb.`dealer_id`, IF( tb.`dealer_id` = '0','All', (SELECT code FROM source_dealer WHERE id = tb.dealer_id) ) as dealer_name, tb.`customer_type` as customer_type_id, IF( `customer_type` = '0','All', IF( `customer_type` = '1', 'Individual', 'Corporation' ) ) as customer_type, tb.`customer_category` as customer_category_id, IF( `customer_category` = '0','All', (SELECT name FROM customer_category WHERE id = tb.customer_category) ) as customer_category, tb.`customer_status` as customer_status_id, IF( `customer_status` = '0','All', IF( `customer_status` = '1', 'Active', 'Inactive' ) ) as customer_status, tb.`activity_date_from`, tb.`activity_date_to`, IF( tb.`activity_date_from` <> '' AND tb.`activity_date_to` <> '' ,CONCAT(tb.`activity_date_from`, ' - ', tb.`activity_date_to`), '' ) as activity_between, tb.`gender_id`, IF( tb.`gender_id` = '0','All', (SELECT gender_name FROM all_gender WHERE id = tb.gender_id) ) as gender_name , tb.`age_from`, tb.`age_to`, IF( tb.`age_from` <> '' AND tb.`age_to` <> '' ,CONCAT(tb.`age_from`, ' - ',tb.`age_to`), '' ) as age_between, tb.state_id, IF( tb.`state_id` = '0','All', (SELECT `provDesc` FROM `all_state` WHERE `provCode` = tb.`state_id` LIMIT 1 ) ) as state_name , tb.city_id, IF( tb.`city_id` = '0','All', (SELECT `citymunDesc` FROM `all_city` WHERE `id` = tb.`city_id`) ) as city_name, tb.`uploaded_date_from`, tb.`uploaded_date_to`, IF( tb.`uploaded_date_from` <> '' AND tb.`uploaded_date_to` <> '' , CONCAT(tb.`uploaded_date_from`, ' - ', tb.`uploaded_date_to`), '' ) as upload_between, tb.`upload_by_employee_id`, IF( tb.`upload_by_employee_id` = '','', (SELECT CONCAT(first_name,' ',middle_name,' ',last_name) FROM `employee` WHERE `id` = tb.`upload_by_employee_id`) ) as upload_by , tb.`customer_data_type`, IF( tb.`customer_data_type` = '0','', tb.`customer_data_type` ) as data_type, tb.message_content, tb.`brand_id`, IF( tb.`brand_id` = '0','All', (SELECT name FROM source_brand WHERE id = tb.brand_id) ) as brand_name, tb.`model_id`, IF( tb.`model_id` = '0','All', (SELECT name FROM source_car_model WHERE id = tb.model_id) ) as model_name, tb.`variant_id`, IF( tb.`variant_id` = '0','All', (SELECT name FROM source_car_model_variant WHERE id = tb.variant_id) ) as variant_name, tb.sort_by, tb.order_by, tb.date_of_birth, tb.insurance_company_id, tb.insurance_type_id, tb.finance_company_id, IF( tb.`insurance_company_id` = '0','All', (SELECT name FROM source_insurance_company WHERE id = tb.insurance_company_id) ) as insurance_company, IF( tb.`insurance_type_id` = '0','All', (SELECT name FROM source_insurance_type WHERE id = tb.insurance_type_id) ) as insurance_type, IF( tb.`finance_company_id` = '0','All', (SELECT name FROM source_finance_company WHERE id = tb.finance_company_id) ) as finance_company, tb.record_count, tb.company_list FROM `text_blast` tb INNER JOIN employee e ON tb.employee_id=e.id INNER JOIN request_type rt ON tb.`request_type_id`=rt.id WHERE tb.id = '".$request_session_data['request_id']."' GROUP BY tb.id "; //echo $parameter_query; return; $request_data = $db -> sql_query($parameter_query); foreach($request_data as $data){ //START GET COMMON DATA OF REPORT $request_session_data['control_number'] = isNotEmpty($data['control_number']) ? $data['control_number'] : 'N/A' ; $request_session_data['request_by'] = isNotEmpty($data['request_by']) ? $data['request_by'] : 'N/A' ; $request_session_data['request_date_time'] = isNotEmpty($data['request_date_time']) ? $data['request_date_time'] : 'N/A' ; $request_session_data['request_type'] = isNotEmpty($data['request_type']) ? $data['request_type'] : '<strong>N/A</strong>' ; $request_session_data['request_type_id'] = isNotEmpty($data['request_type_id']) ? $data['request_type_id'] : '<strong>N/A</strong>' ; $request_session_data['request_between'] = isNotEmpty($data['activity_between']) ? $data['activity_between'] : 'N/A' ; $request_session_data['dms_name'] = isNotEmpty($data['dms_name']) ? $data['dms_name'] : 'N/A' ; $request_session_data['company_name'] = isNotEmpty($data['company_name']) ? $data['company_name'] : 'N/A' ; $request_session_data['dealer_name'] = isNotEmpty($data['dealer_name']) ? $data['dealer_name'] : 'N/A' ; $request_session_data['customer_type'] = isNotEmpty($data['customer_type']) ? $data['customer_type'] : 'N/A' ; $request_session_data['customer_status'] = isNotEmpty($data['customer_status']) ? $data['customer_status'] : 'N/A' ; $request_session_data['customer_category'] = isNotEmpty($data['customer_category']) ? $data['customer_category'] : 'N/A' ; $request_session_data['gender_name'] = isNotEmpty($data['gender_name']) ? $data['gender_name'] : 'N/A' ; // $report_session_data['brand_name'] = isNotEmpty($data['brand_name']) ? $data['brand_name'] : 'N/A' ; // $report_session_data['model_name'] = isNotEmpty($data['model_name']) ? $data['model_name'] : 'N/A' ; $request_session_data['brand_name'] = ($data['brand_name'] <> '0' ) ? $data['brand_name'] : 'All' ; $request_session_data['model_name'] = ($data['model_name'] <> '0') ? $data['model_name'] : 'All' ; $request_session_data['variant_name'] = ($data['variant_name'] <> '0') ? $data['variant_name'] : 'All' ; $request_session_data['age_between'] = isNotEmpty($data['age_between']) ? $data['age_between'] : 'N/A' ; $request_session_data['state_name'] = isNotEmpty($data['state_name']) ? $data['state_name'] : 'N/A' ; $request_session_data['city_name'] = isNotEmpty($data['city_name']) ? $data['city_name'] : 'N/A' ; $request_session_data['upload_between'] = isNotEmpty($data['upload_between']) ? $data['upload_between'] : 'N/A' ; $request_session_data['upload_by'] = isNotEmpty($data['upload_by']) ? $data['upload_by'] : 'N/A' ; $request_session_data['data_type'] = isNotEmpty($data['data_type']) ? $data['data_type'] : 'N/A' ; $request_session_data['date_of_birth'] = isNotEmpty($data['date_of_birth']) ? $data['date_of_birth'] : 'N/A' ; $request_session_data['message_content'] = isNotEmpty($data['message_content']) ? $data['message_content'] : 'N/A' ; $request_session_data['insurance_company'] = isNotEmpty($data['insurance_company']) ? $data['insurance_company'] : 'N/A' ; $request_session_data['insurance_type'] = isNotEmpty($data['insurance_type']) ? $data['insurance_type'] : 'N/A' ; $request_session_data['finance_company'] = isNotEmpty($data['finance_company']) ? $data['finance_company'] : 'N/A' ; $request_session_data['record_count'] = isNotEmpty($data['record_count']) ? $data['record_count'] : '0' ; $dms_id = $data['dms_id']; $company_id = $data['company_id']; $dealer_id = $data['dealer_id']; $age_from = $data['age_from']; $age_to = $data['age_to']; $gender_id = $data['gender_id']; $state_id = $data['state_id']; $city_id = $data['city_id']; $brand_id = $data['brand_id']; $model_id = $data['model_id']; $variant_id = $data['variant_id']; $insurance_company_id = $data['insurance_company_id']; $insurance_type_id = $data['insurance_type_id']; $finance_company_id = $data['finance_company_id']; $upload_by_id = $data['upload_by_employee_id']; $customer_data_type = $data['customer_data_type']; $customer_type = $data['customer_type_id']; $customer_category = $data['customer_category_id']; $customer_status = $data['customer_status_id']; $sort_by = $data['sort_by']; $order_by = $data['order_by']; $company_list = $data['company_list']; //dates format $activity_date_from = fix_date($data['activity_date_from'], false); $activity_date_to = fix_date($data['activity_date_to'],false); $upload_date_from = fix_date($data['uploaded_date_from'], false); $upload_date_to = fix_date($data['uploaded_date_to'],false); $filter_date_of_birth = fix_date($data['date_of_birth'],false); //END GET COMMON DATA OF REPORT $request_session_data['file_name'] = strtr($db -> cipher($request_session_data['control_number'], true) , "+/=", "-_~" ).'.'.'pdf'; // echo $request_session_data['file_name']; return; if(file_exists('sms_service/pdf/' . $request_session_data['file_name'])){ // echo "Existing"; return; //$request_session_data['col_width'] = array(15, 35, 22, 15,20 ,8, 38, 25, 22); switch($data['request_type_id']){ //60+40+45+35 case "1": //$request_session_data['col_width'] = array(15, 35, 22, 15,20 ,8, 38, 25, 22); $request_session_data['col_width'] = array(17, 57, 43, 37, 46); break; case "2": $request_session_data['col_width'] = array(15, 40, 25, 28, 20 ,25, 25, 22); break; case "3": $request_session_data['col_width'] = array(15, 20, 37, 20, 23, 15, 20 ,18, 18 , 14); break; case "4": $request_session_data['col_width'] = array(15, 20, 35, 17, 23, 20, 20 ,18, 18 , 14); break; } $request_count_data = $request_session_data['record_count']; } else { // echo "Not existing"; return; switch($data['request_type_id']){ case "1": //$request_session_data['col_width'] = array(15, 35, 22, 15,20 ,8, 38, 25, 22); $request_session_data['col_width'] = array(17, 57, 43, 37, 46); $request_count_data = getFilteredCustomerData($company_list,$insurance_company_id,$insurance_type_id,$finance_company_id,$request_session_data['control_number'],$customer_category,$customer_status,$fmt,$filter_date_of_birth,$order_by,$sort_by,$brand_id,$model_id,$variant_id,$customer_type,$customer_data_type,$upload_by_id,$state_id,$city_id,$gender_id,$age_from,$age_to,$dms_id,$company_id,$dealer_id,$activity_date_from,$activity_date_to,$upload_date_from,$upload_date_to,$db); break; case "2": $request_session_data['col_width'] = array(15, 40, 25, 28, 20 ,25, 25, 22); $request_count_data = getFilteredVehicleData($company_list,$insurance_company_id,$insurance_type_id,$finance_company_id,$request_session_data['control_number'],$customer_category,$customer_status,$fmt,$filter_date_of_birth,$order_by,$sort_by,$brand_id,$model_id,$variant_id,$customer_type,$customer_data_type,$upload_by_id,$state_id,$city_id,$gender_id,$age_from,$age_to,$dms_id,$company_id,$dealer_id,$activity_date_from,$activity_date_to,$upload_date_from,$upload_date_to,$db); break; case "3": $request_session_data['col_width'] = array(15, 20, 37, 20, 23, 15, 20 ,18, 18 , 14); $request_count_data = getFilteredServiceData($company_list,$insurance_company_id,$insurance_type_id,$finance_company_id,$request_session_data['control_number'],$customer_category,$customer_status,$fmt,$filter_date_of_birth,$order_by,$sort_by,$brand_id,$model_id,$variant_id,$customer_type,$customer_data_type,$upload_by_id,$state_id,$city_id,$gender_id,$age_from,$age_to,$dms_id,$company_id,$dealer_id,$activity_date_from,$activity_date_to,$upload_date_from,$upload_date_to,$db); break; case "4": $request_session_data['col_width'] = array(15, 20, 35, 17, 23, 20, 20 ,18, 18 , 14); $request_count_data = getFilteredSalesData($company_list,$insurance_company_id,$insurance_type_id,$finance_company_id,$request_session_data['control_number'],$customer_category,$customer_status,$fmt,$filter_date_of_birth,$order_by,$sort_by,$brand_id,$model_id,$variant_id,$customer_type,$customer_data_type,$upload_by_id,$state_id,$city_id,$gender_id,$age_from,$age_to,$dms_id,$company_id,$dealer_id,$activity_date_from,$activity_date_to,$upload_date_from,$upload_date_to,$db); break; } } }//end foreach // echo $_SESSION['customer_data']; // $_SESSION['vehicle_data'] = json_encode($customer_data); // $_SESSION['service_data'] = json_encode($customer_data); // $_SESSION['sale_data'] = json_encode($customer_data); $return_array['status'] = 1; $return_array['message'] = "Request Generated"; } else { $return_array['status'] = 0; $return_array['message'] = "Request Generation Failed"; } // FUNCTIONS HERE function getFilteredCustomerData($company_list, $insurance_company_id, $insurance_type_id, $finance_company_id, $control_number, $customer_category, $customer_status, $fmt, $filter_date_of_birth, $order_by, $sort_by, $brand_id, $model_id, $variant_id, $customer_type, $customer_data_type, $upload_by_id, $state_id, $city_id, $gender_id, $age_from, $age_to, $dms_id, $company_id, $dealer_id, $activity_date_from, $activity_date_to, $upload_date_from, $upload_date_to, $db){ if(isNotEmpty($upload_date_from) && isNotEmpty($upload_date_to)){ $filter_date_upload = " AND c.date_uploaded BETWEEN DATE_FORMAT('$upload_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$upload_date_to', '%Y-%m-%d 23:59:00') "; } else { $filter_date_upload = ""; } if(isNotEmpty($activity_date_from) && isNotEmpty($activity_date_to)){ $filter_date_created = " AND c.date_created BETWEEN DATE_FORMAT('$activity_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$activity_date_to', '%Y-%m-%d 23:59:00') "; } else { $filter_date_created = ""; } if(isNotEmpty($age_from) && isNotEmpty($age_to)){ $filter_age = " AND TIMESTAMPDIFF(YEAR,c.date_of_birth, CURDATE()) BETWEEN '$age_from' AND '$age_to' "; }else{ $filter_age = ""; } if(isNotEmpty($upload_by_id)){ $filter_upload_by = " AND c.upload_by = '$upload_by_id' "; }else{ $filter_upload_by = ""; } $company = ($company_id != '0') ? " AND scd.company_id = '$company_id' " : " AND scd.company_id IN (".$company_list.") " ; $dealer = ($dealer_id != '0') ? " AND scd.dealer_id = '$dealer_id' " : "" ; $gender =($gender_id != '0') ? " AND c.gender_id = '".$gender_id."' " : "" ; $dms =($dms_id != '0') ? " AND sc.dms_id = '".$dms_id."' " : "" ; $state = ($state_id != '0') ? " AND cc.address_1_state = '$state_id' " : "" ; $city = ($city_id != '0') ? " AND cc.address_1_city = '$city_id' " : "" ; $customertype = ($customer_type != '0') ? " AND c.type = '$customer_type' " : "" ; $customercategory = ($customer_category != '0') ? " HAVING customer_category = '$customer_category' " : "" ; $customerstatus = ($customer_status != '0') ? " ".(($customer_category != '0') ? " AND " : " HAVING " )." isActive = '$customer_status' " : "" ; //default $brand = ($brand_id != '0') ? " AND v.brand_id = '$brand_id' " : "" ; $model = ($model_id != '0') ? " AND v.model_id = '$model_id' " : "" ; $variant = ($variant_id != '0') ? " AND v.model_variant_description = '$variant_id' " : ""; $complete_data = ""; $incomplete_data = ""; $mobile_phone_1 = ""; $email_1 = ""; $address_1 = ""; $date_of_birth =""; if(isNotEmpty($customer_data_type) && $customer_data_type != '0'){ switch($customer_data_type){ case "Complete Data": $complete_data = " AND ((c.date_of_birth <> '' and ag.id <> 0 and cc.address_1 <> '' and cc.email_1 <> '' and cc.mobile_phone_1 <> '') AND c.type = 1) OR (cc.address_1 <> '' AND cc.email_1 <> '' AND c.type = 2) "; break; case "Incomplete Data": $incomplete_data = " AND ((c.date_of_birth = '' OR ag.id = 0 OR cc.address_1 = '' OR cc.email_1 = '') AND c.type = 1) OR (cc.address_1 = '' or cc.email_1 = '' AND c.type = 2) "; break; case "No Mobile": $mobile_phone_1 = " AND cc.mobile_phone_1 = '' "; break; case "No Email": $email_1 = " AND cc.email_1 = '' "; break; case "No Address": $address_1 = " AND cc.address_1 = '' "; break; case "No Gender": $gender = " AND ag.gender_id = '0' AND c.type = '1' "; break; case "No Date of birth": $date_of_birth = " AND c.date_of_birth = '' AND c.type = '1' "; break; } } $insurance_company = ($insurance_company_id != '0') ? " AND (s.insurance_company_id = '$insurance_company_id') " : "" ; $insurance_type = ($insurance_type_id != '0') ? " AND (s.insurance_type_id = '$insurance_type_id') " : "" ; $finance_company = ($finance_company_id != '0') ? " AND (s.finance_company_id = '$finance_company_id') " : "" ; if(isNotEmpty($filter_date_of_birth) && isNotEmpty($filter_date_of_birth)){ $filter_dob = " AND c.date_of_birth = '$filter_date_of_birth' "; }else{ $filter_dob = ""; } switch(intval($sort_by)){ case 1: //lastname $filter_sort_by = " c.last_name "; break; case 2: //firstname $filter_sort_by = " c.first_name "; break; case 3: //corporation name $filter_sort_by = " c.corporation_name "; break; case 4: //date created $filter_sort_by = " c.date_created "; break; case 5: //date uploaded $filter_sort_by = " c.date_uploaded "; break; } $return_data = array(); $query = " SELECT %s FROM customer c INNER JOIN customer_category ccat ON c.category_id = ccat.id INNER JOIN customer_contact cc ON c.id = cc.customer_id INNER JOIN employee e ON c.upload_by = e.id INNER JOIN customer_dms cd ON c.id = cd.customer_record_id INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN source_company sc ON scd.company_id = sc.id INNER JOIN all_gender ag ON c.gender_id = ag.id INNER JOIN all_marital_status am ON c.marital_status_id = am.id INNER JOIN all_countries ac ON c.nationality_id = ac.num_code INNER JOIN all_suffix asx ON c.suffix_id = asx.id LEFT OUTER JOIN vehicle v ON c.id = v.customer_record_id LEFT OUTER JOIN sales s ON v.id = s.vehicle_id LEFT OUTER JOIN service se ON v.id = se.vehicle_id WHERE 1 %s / upload_date / %s / date_created / %s / dms / %s / company / %s / dealer / %s / age / %s / gender / %s / state / %s / city / %s / upload_by / %s / complete_data / %s / incomplete_id / %s / no_address / %s / no_dob / %s / type / %s / dob / %s / brand / %s / model / %s / insurance_company / %s / insurance_type / %s / finance_company / AND c.status = 1 GROUP BY c.id %s %s ORDER BY $filter_sort_by $order_by "; // LEFT OUTER JOIN source_brand sbv // ON v.brand_id=sbv.id // LEFT OUTER JOIN source_car_model scmv // ON v.model_id=scmv.id // LEFT OUTER JOIN source_brand sbs // ON s.brand_id=sbs.id // LEFT OUTER JOIN source_car_model scms // ON s.model_id=scms.id // LEFT OUTER JOIN source_brand sbse // ON se.brand_id=sbse.id // LEFT OUTER JOIN source_car_model scmse // ON se.model_id=scmse.id $fields = " c.id, c.photo, IF(c.type = 1, concat(c.first_name,' ',c.middle_name,' ',c.last_name, ' ', IF(c.suffix_id < 1, '',asx.suffix_name )) , c.corporation_name ) AS name, am.name as marital_status, ag.gender_name, c.date_of_birth, 'Individual' as customer_type, c.date_uploaded, TIMESTAMPDIFF(YEAR,c.date_of_birth, CURDATE()) as age_number, c.date_created, cc.mobile_phone_1, cc.mobile_phone_2, cc.email_1, cc.email_2, cc.business_phone, cc.fax_phone, cc.residential_phone, cc.address_1, c.contact_person, ac.nationality, c.occupation, c.no_of_children, concat(e.first_name,' ',e.last_name) as upload_by, c.type, (IF(c.category_id <> 0, ccat.id , IF(COUNT(se.id) > 0 && COUNT(s.id) < 1, '1' , IF(COUNT(se.id) < 1 && COUNT(s.id) > 0, '2' , IF(COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, '3' , IF(COUNT(v.id) > 1 && COUNT(v.id) < 4, '4' , IF(COUNT(v.id) > 3, '5' , '0'/VVIP/ ) ) ) ) ) )) as customer_category, IF((SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) \|\| (SELECT DATEDIFF(NOW(),s.activity_date) <= 365), '1' , '2' ) AS isActive"; $count = " (IF(c.category_id <> 0, ccat.id , IF(COUNT(se.id) > 0 && COUNT(s.id) < 1, '1' , IF(COUNT(se.id) < 1 && COUNT(s.id) > 0, '2' , IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, '3' , IF( COUNT(v.id) > 1 && COUNT(v.id) < 4, '4' , IF( COUNT(v.id) > 3, '5' , '0'/VVIP/ ) ) ) ) ) )) AS customer_category, IF((SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) \|\| (SELECT DATEDIFF(NOW(),s.activity_date) <= 365), '1' , '2' ) AS isActive"; // echo sprintf($query,$fields,$filter_date_upload,$filter_date_created,$dms,$company,$dealer,$filter_age,$gender,$state,$city,$filter_upload_by, $complete_data,$incomplete_data,$address_1,$date_of_birth,$customertype,$filter_dob,$brand,$model,$variant,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus); return; //echo $brand; exit; $data_list = $db -> sql_query(sprintf($query,$fields,$filter_date_upload,$filter_date_created,$dms,$company,$dealer,$filter_age,$gender,$state,$city,$filter_upload_by, $complete_data,$incomplete_data,$address_1,$date_of_birth,$customertype,$filter_dob,$brand,$model,$variant,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)); $query_count = $db -> select("SELECT COUNT(1) FROM ( ".sprintf($query,$count,$filter_date_upload,$filter_date_created,$dms,$company,$dealer,$filter_age,$gender,$state,$city,$filter_upload_by, $complete_data,$incomplete_data,$address_1,$date_of_birth,$customertype,$filter_dob,$brand,$model,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus) ." ) as total_count"); $filename = 'sms_service/data/'.strtr($db -> cipher($control_number, true), "+/", "-_" ).'.'.'php'; $fp = fopen($filename, 'w'); $col_header = array('No.', 'Name', 'Type', 'Gender', 'Mobile'); $string = implode(';',array_map('replaceExistingSemiColon', $col_header)); fwrite($fp, $string.PHP_EOL); $record_counter = 0; if( $query_count > 0){ $record_counter++; foreach ($data_list as $data){ $involvements = "SELECT sd.name AS dms, sc.code AS company, sdr.code AS dealer FROM `customer_dms` cd INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id INNER JOIN source_company sc ON sc.id = scd.company_id INNER JOIN source_dms sd ON sc.`dms_id` = sd.id INNER JOIN source_dealer sdr ON sdr.id = scd.dealer_id WHERE `customer_record_id` IN ('".$data['id']."')"; //echo $involvements; exit; $in = $db->return_result($involvements); $dms = array(); $company = array(); $dealer = array(); foreach($in as $data1){ $dms = array_merge($dms, array($data1['dms'])); $company = array_merge($company, array($data1['company'])); $dealer = array_merge($dealer, array($data1['dealer'])); } $dms_new = array_unique($dms); $company_new = array_unique($company); $dealer_new = array_unique($dealer); $list_dms = implode(', ', $dms_new); $list_company = implode(', ', $company_new); $list_dealer = implode(', ', $dealer_new); $data_to_insert = array(); // $return_data = array_merge($return_data,array(array('No.'=>$record_counter++,'Name'=>$data['name'],'Age'=>$data['age_number'],'Gender'=>$data['gender_name'],'Email'=>$data['email_1'],'Mobile'=>$data['mobile_phone_1'],'Type'=>(intval($data['type']) === 1) ? 'Individual' : 'Corporation' ))); if(intval($data['type']) === 1){ $data_to_insert = array( $record_counter++, $data['name'], (intval($data['type']) === 1) ? 'Individual' : 'Corporation', $data['gender_name'], //$data['date_of_birth'], //$data['age_number'], // $data['email_1'], "+" . mask($data['mobile_phone_1'], 5, 1) // $list_dms, // $list_company, // $list_dealer, // $data['date_created'], ); } else { $data_to_insert = array( $record_counter++, $data['name'], (intval($data['type']) === 1) ? 'Individual' : 'Corporation', '', // $data['date_of_birth'], // $data['age_number'], // $data['email_1'], "+" . mask($data['mobile_phone_1'], 5, 1) // $list_dms, // $list_company, // $list_dealer, // $data['date_created'], ); } $string = implode(';',array_map('replaceExistingSemiColon', $data_to_insert)); //echo $data_to_insert; exit; fwrite($fp, $string.PHP_EOL); } fclose($fp); }else{ // $return_data = array_merge($return_data,array(array('Name'=>'','Age'=>'','Gender'=>'','Email'=>'','Mobile'=>'','Type'=>''))); } $db -> sql_query("UPDATE text_blast SET record_count = '".(max(0,($record_counter-1)))."' WHERE control_number = '$control_number' "); return max(0,($record_counter-1));//array($return_data,array($record_counter)); } ////////////////////////////////////////////////////////////////////////////////// // function getFilteredVehicleData($company_list,$insurance_company_id,$insurance_type_id,$finance_company_id,$control_number,$customer_category,$customer_status,$fmt,$filter_date_of_birth,$order_by,$sort_by,$brand_id,$model_id,$customer_type,$customer_data_type,$upload_by_id,$state_id,$city_id,$gender_id,$age_from,$age_to,$dms_id,$company_id,$dealer_id,$activity_date_from,$activity_date_to,$upload_date_from,$upload_date_to,$db){ // if(isNotEmpty($upload_date_from) && isNotEmpty($upload_date_to)){ // $filter_date_upload = " AND v.date_uploaded BETWEEN DATE_FORMAT('$upload_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$upload_date_to', '%Y-%m-%d 23:59:00') "; // }else{ // $filter_date_upload = ""; // } // if(isNotEmpty($activity_date_from) && isNotEmpty($activity_date_to)){ // $filter_date_created = " AND v.date_created BETWEEN DATE_FORMAT('$activity_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$activity_date_to', '%Y-%m-%d 23:59:00') "; // }else{ // $filter_date_created = ""; // } // if(isNotEmpty($age_from) && isNotEmpty($age_to)){ // $filter_age = " AND TIMESTAMPDIFF(YEAR,c.date_of_birth, CURDATE()) BETWEEN '$age_from' AND '$age_to' "; // }else{ // $filter_age = ""; // } // if(isNotEmpty($upload_by_id)){ // $filter_upload_by = " AND v.upload_by = '$upload_by_id' "; // }else{ // $filter_upload_by = ""; // } // $dms = ($dms_id != '0') ? " AND v.dms_id = '$dms_id' " : "" ; // $company = ($company_id != '0') ? " AND scd.company_id = '$company_id' " : " AND scd.company_id IN (".$company_list.") " ; // $dealer = ($dealer_id != '0') ? " AND scd.dealer_id = '$dealer_id' " : "" ; // $state = ($state_id != '0') ? " AND cc.address_1_state = '$state_id' " : "" ; // $city = ($city_id != '0') ? " AND cc.address_1_city = '$city_id' " : "" ; // $gender = ($gender_id != '0') ? " AND c.gender_id = '$gender_id' " : "" ; // $customertype = ($customer_type != '0') ? " AND c.type = '$customer_type' " : "" ; // $brand = ($brand_id != '0') ? " AND v.id = '$brand_id' " : "" ; // $model = ($model_id != '0') ? " AND v.id = '$model_id' " : "" ; // $variant = ($variant_id != '0') ? " AND v.id = '$variant_id' " : "" ; // $insurance_company = ($insurance_company_id != '0') ? " AND ( s.insurance_company_id = '$insurance_company_id') " : "" ; // $insurance_type = ($insurance_type_id != '0') ? " AND ( s.insurance_type_id = '$insurance_type_id') " : "" ; // $finance_company = ($finance_company_id != '0') ? " AND ( s.finance_company_id = '$finance_company_id') " : "" ; // $complete_data = ""; // $incomplete_data = ""; // $address_1 = ""; // $date_of_birth =""; // //disabled function below // if(isNotEmpty($customer_data_type) && $customer_data_type != '0'){ // switch($customer_data_type){ // case "Complete Data": // $complete_data = " AND ((c.date_of_birth <> '' and ag.id <> 0 and cc.address_1 <> '' and cc.email_1 <> '' and cc.mobile_phone_1 <> '') AND c.type = 1) // OR // (cc.address_1 <> '' AND cc.email_1 <> '' AND c.type = 2) "; // break; // case "Incomplete Data": // $incomplete_data = " AND ((c.date_of_birth = '' OR // ag.id = 0 OR // cc.address_1 = '' OR cc.email_1 = '') AND c.type = 1) OR (cc.address_1 = '' or cc.email_1 = '' AND c.type = 2) "; // break; // case "No Mobile": // $mobile_phone_1 = " AND cc.mobile_phone_1 = '' "; // break; // case "No Email": // $email_1 = " AND cc.email_1 = '' "; // break; // case "No Address": // $address_1 = " AND cc.address_1 = '' "; // break; // case "No Gender": // $gender = " AND ag.gender_id = '0' AND c.type = '1' "; // break; // case "No Date of birth": // $date_of_birth = " AND c.date_of_birth = '' AND c.type = '1' "; // break; // } // } // if(isNotEmpty($filter_date_of_birth) && isNotEmpty($filter_date_of_birth)){ // $filter_dob = " AND c.date_of_birth = '$filter_date_of_birth' "; // }else{ // $filter_dob = ""; // } // switch(intval($sort_by)){ // case 1: //lastname // $filter_sort_by = " c.last_name "; // break; // case 2: //firstname // $filter_sort_by = " c.first_name "; // break; // case 3: //corporation name // $filter_sort_by = " c.corporation_name "; // break; // case 4: //date created // $filter_sort_by = " v.date_created "; // break; // case 5: //date uploaded // $filter_sort_by = " v.date_uploaded "; // break; // } // $customercategory = ($customer_category != '0') ? " HAVING customer_category = '$customer_category' " : "" ; // $customerstatus = ($customer_status != '0') ? " ".(($customer_category != '0') ? " AND " : " HAVING " )." isActive = '$customer_status' " : "" ; // $return_data = array(); // $query = "SELECT %s FROM vehicle v // INNER JOIN source_brand sb ON v.brand_id=sb.id // INNER JOIN source_car_model scm ON v.model_id=scm.id // INNER JOIN vehicle_color vc ON v.color_id=vc.id // INNER JOIN source_company_dealer scd ON scd.id=v.company_dealer_id // INNER JOIN source_company sc ON sc.id = scd.company_id // INNER JOIN source_dealer sd ON sd.id= scd.dealer_id // INNER JOIN source_dms sdms ON v.dms_id = sdms.id // INNER JOIN employee e ON v.upload_by = e.id // LEFT OUTER JOIN customer c ON v.customer_record_id = c.id // LEFT OUTER JOIN customer_contact cc ON c.id = cc.customer_id // LEFT OUTER JOIN customer_category ccat ON c.category_id = ccat.id // LEFT OUTER JOIN sales s // ON v.id = s.vehicle_id // LEFT OUTER JOIN service se // ON v.id = se.vehicle_id // WHERE 1 // %s / brand / // %s / model / // %s / customer type / // %s / upload by / // %s / state / // %s / city / // %s / gender / // %s / filter age / // %s / dms type / // %s / company / // %s / dealer / // %s / filter_date_upload / // %s / filter_date_created / // %s / filter_dob / // %s / insurance_company / // %s / insurance_type / // %s / finance_company / // AND v.status = 1 GROUP BY v.id // %s // %s // ORDER BY $filter_sort_by $order_by"; // $count = " ( // IF( c.category_id <> 0,ccat.id, // IF( COUNT(se.id) > 0 && COUNT(s.id) < 1, // '1', // IF( COUNT(se.id) < 1 && COUNT(s.id) > 0, // '2', // IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, // '3', // IF( COUNT(v.id) > 1 && COUNT(v.id) < 4, // '4', // IF( COUNT(v.id) > 3, // '5','0'/VVIP/ // ) // ) // ) // ) // ) // ) // ) as customer_category, // IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) \|\| (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) , // '1','2' // ) // as isActive"; // $fields = " v.id, scm.name AS model, v.plate_number, v.conduction_sticker, sb.name AS brand, vc.name AS color, v.customer_id, // vc.name as color_name,sc.code as company_name, sd.code as dealer_name,sdms.name as dms_name,v.vehicle_release_year,v.engine_type,v.gas_type,v.transmission,v.chassis_number,v.serial_number,v.vin_number,v.sales_person, // scm.model_variant_description,v.date_uploaded,concat(e.first_name,' ',e.last_name) as upload_by, v.date_created, // IF( // c.type = 1, concat(c.first_name,' ',c.middle_name,' ',c.last_name),c.corporation_name // ) as name, // ( // IF( c.category_id <> 0,ccat.id, // IF( COUNT(se.id) > 0 && COUNT(s.id) < 1, // '1', // IF( COUNT(se.id) < 1 && COUNT(s.id) > 0, // '2', // IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, // '3', // IF( COUNT(v.id) > 1 && COUNT(v.id) < 4, // '4', // IF( COUNT(v.id) > 3, // '5','0'/VVIP/ // ) // ) // ) // ) // ) // ) // ) as customer_category, // IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) \|\| (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) , // '1','2' // ) // as isActive // "; // // echo sprintf($query, $fields,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus);exit; // $data_list = $db -> sql_query(sprintf($query, $fields,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)); // $query_count = $db -> select("SELECT COUNT() FROM ( ".sprintf($query, $count,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)." ) as total_count"); // $filename = 'sms_service/data/'.strtr($db -> cipher($control_number,true), "+/", "-_" ).'.'.'php'; // $fp = fopen($filename, 'w'); // $col_header = array('No.','Owner','Brand','Model', 'Color', 'CS','Plate','Date Created'); // $string = implode(';',array_map('replaceExistingSemiColon',$col_header)); // fwrite($fp,$string.PHP_EOL); // $record_counter = 0; // if( $query_count > 0){ // $record_counter++; // foreach ($data_list as $data){ // // $return_data = array_merge($return_data,array(array('No.'=>$record_counter++,'Brand'=>$data['brand'],'Model'=>$data['model'],'CS Number'=>$data['conduction_sticker'],'Plate Number'=>$data['plate_number'],'Owner'=>$data['name']))); // // $return_data = array_merge($return_data,array(array( // // 'No.'=>$record_counter++, // // 'Owner'=>$data['name'], // // 'Brand'=>$data['brand'], // // 'Model'=>$data['model'], // // // 'Model Desc.'=>$data['model_variant_description'], // // 'Color'=>$data['color_name'], // // 'CS'=>$data['conduction_sticker'], // // 'Plate'=>$data['plate_number'], // // // 'Year Model'=>$data['vehicle_release_year'], // // // 'Engine Type'=>$data['engine_type'], // // // 'Gas Type'=>$data['gas_type'], // // // 'Transmission'=>$data['transmission'], // // // 'VIN Number'=>$data['vin_number'], // // // 'Serial Number'=>$data['serial_number'], // // // 'Chassis Number'=>$data['chassis_number'], // // // 'Sales Person'=>$data['sales_person'], // // // 'DMS'=>$data['dms_name'], // // 'Company'=>$data['company_name'], // // 'Dealer'=>$data['dealer_name'], // // 'Date'=>$data['date_created'], // // // 'Upload Date'=>$data['date_uploaded'], // // // 'Upload By'=>$data['upload_by'], // // ))); // $data_to_insert = array( // $record_counter++, // $data['name'], // $data['brand'], // $data['model'], // $data['color_name'], // $data['conduction_sticker'], // $data['plate_number'], // $data['date_created'], // ); // $string = implode(';',array_map('replaceExistingSemiColon',$data_to_insert)); // fwrite($fp,$string.PHP_EOL); // } // fclose($fp); // }else{ // // $return_data = array_merge($return_data,array(array('Brand'=>'','Model'=>'','CS Number'=>'','Plate Number'=>'','Owner'=>''))); // } // // return array($return_data,array($query_count)); // $db -> sql_query("UPDATE request SET record_count = '".(max(0,($record_counter-1)))."' WHERE control_number = '$control_number' "); // return max(0,($record_counter-1));//array($return_data,array($record_counter)); // } //////////////////////////////////////////////////////////////////// // function getFilteredServiceData($company_list,$insurance_company_id,$insurance_type_id,$finance_company_id,$control_number,$customer_category,$customer_status,$fmt,$filter_date_of_birth,$order_by,$sort_by,$brand_id,$model_id,$customer_type,$customer_data_type,$upload_by_id,$state_id,$city_id,$gender_id,$age_from,$age_to,$dms_id,$company_id,$dealer_id,$activity_date_from,$activity_date_to,$upload_date_from,$upload_date_to,$db){ // if(isNotEmpty($upload_date_from) && isNotEmpty($upload_date_to)){ // $filter_date_upload = " AND se.date_uploaded BETWEEN DATE_FORMAT('$upload_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$upload_date_to', '%Y-%m-%d 23:59:00') "; // }else{ // $filter_date_upload = ""; // } // if(isNotEmpty($activity_date_from) && isNotEmpty($activity_date_to)){ // $filter_date_created = " AND se.start_date_of_service BETWEEN DATE_FORMAT('$activity_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$activity_date_to', '%Y-%m-%d 23:59:00') "; // }else{ // $filter_date_created = ""; // } // if(isNotEmpty($age_from) && isNotEmpty($age_to)){ // $filter_age = " AND TIMESTAMPDIFF(YEAR,c.date_of_birth, CURDATE()) BETWEEN '$age_from' AND '$age_to' "; // }else{ // $filter_age = ""; // } // if(isNotEmpty($upload_by_id)){ // $filter_upload_by = " AND se.upload_by = '$upload_by_id' "; // }else{ // $filter_upload_by = ""; // } // $dms = ($dms_id != '0') ? " AND se.dms_id = '$dms_id' " : "" ; // $company = ($company_id != '0') ? " AND scd.company_id = '$company_id' " : " AND scd.company_id IN (".$company_list.") " ; // $dealer = ($dealer_id != '0') ? " AND scd.dealer_id = '$dealer_id' " : "" ; // $state = ($state_id != '0') ? " AND cc.address_1_state = '$state_id' " : "" ; // $city = ($city_id != '0') ? " AND cc.address_1_city = '$city_id' " : "" ; // $gender = ($gender_id != '0') ? " AND c.gender_id = '$gender_id' " : "" ; // $customertype = ($customer_type != '0') ? " AND c.type = '$customer_type' " : "" ; // $brand = ($brand_id != '0') ? " AND sb.id = '$brand_id' " : "" ; // $model = ($model_id != '0') ? " AND scm.id = '$model_id' " : "" ; // $insurance_company = ($insurance_company_id != '0') ? " AND s.insurance_company_id = '$insurance_company_id' " : "" ; // $insurance_type = ($insurance_type_id != '0') ? " AND s.insurance_type_id = '$insurance_type_id' " : "" ; // $finance_company = ($finance_company_id != '0') ? " AND s.finance_company_id = '$finance_company_id' " : "" ; // $complete_data = ""; // $incomplete_data = ""; // $address_1 = ""; // $date_of_birth =""; // //disabled function below // if(isNotEmpty($customer_data_type) && $customer_data_type != '0'){ // switch($customer_data_type){ // case "Complete Data": // $complete_data = " AND ((c.date_of_birth <> '' and ag.id <> 0 and cc.address_1 <> '' and cc.email_1 <> '' and cc.mobile_phone_1 <> '') AND c.type = 1) // OR // (cc.address_1 <> '' AND cc.email_1 <> '' AND c.type = 2) "; // break; // case "Incomplete Data": // $incomplete_data = " AND ((c.date_of_birth = '' OR // ag.id = 0 OR // cc.address_1 = '' OR cc.email_1 = '') AND c.type = 1) OR (cc.address_1 = '' or cc.email_1 = '' AND c.type = 2) "; // break; // case "No Mobile": // $mobile_phone_1 = " AND cc.mobile_phone_1 = '' "; // break; // case "No Email": // $email_1 = " AND cc.email_1 = '' "; // break; // case "No Address": // $address_1 = " AND cc.address_1 = '' "; // break; // case "No Gender": // $gender = " AND ag.gender_id = '0' AND c.type = '1' "; // break; // case "No Date of birth": // $date_of_birth = " AND c.date_of_birth = '' AND c.type = '1' "; // break; // } // } // if(isNotEmpty($filter_date_of_birth) && isNotEmpty($filter_date_of_birth)){ // $filter_dob = " AND c.date_of_birth = '$filter_date_of_birth' "; // }else{ // $filter_dob = ""; // } // switch(intval($sort_by)){ // case 1: //lastname // $filter_sort_by = " c.last_name "; // break; // case 2: //firstname // $filter_sort_by = " c.first_name "; // break; // case 3: //corporation name // $filter_sort_by = " c.corporation_name "; // break; // case 4: //date created // $filter_sort_by = " se.start_date_of_service "; // break; // case 5: //date uploaded // $filter_sort_by = " se.date_uploaded "; // break; // } // $customercategory = ($customer_category != '0') ? " HAVING customer_category = '$customer_category' " : "" ; // $customerstatus = ($customer_status != '0') ? " ".(($customer_category != '0') ? " AND " : " HAVING " )." isActive = '$customer_status' " : "" ; // $return_data = array(); // $query = "SELECT %s FROM service se // INNER JOIN vehicle v ON se.vehicle_id = v.id // INNER JOIN sales s ON s.vehicle_id = v.id // INNER JOIN source_brand sb ON v.brand_id=sb.id // INNER JOIN source_car_model scm ON v.model_id=scm.id // INNER JOIN source_company_dealer scd ON scd.id=se.company_dealer_id // INNER JOIN source_company sc ON sc.id = scd.company_id // INNER JOIN source_dealer sd ON sd.id= scd.dealer_id // INNER JOIN employee e ON se.upload_by = e.id // INNER JOIN source_dms sdms ON se.dms_id = sdms.id // LEFT OUTER JOIN customer c ON v.customer_record_id = c.id // LEFT OUTER JOIN customer_contact cc ON c.id = cc.customer_id // LEFT OUTER JOIN customer_category ccat ON c.category_id = ccat.id // WHERE 1 // %s /* brand / // %s / model / // %s / customer type / // %s / upload by / // %s / state / // %s / city / // %s / gender / // %s / filter age / // %s / dms type / // %s / company / // %s / dealer / // %s / filter_date_upload / // %s / filter_date_created / // %s / filter_dob/ // %s / insurance_company / // %s / insurance_type / // %s / finance_company / // AND se.status = 1 GROUP BY se.id %s %s ORDER BY $filter_sort_by $order_by"; // $count = "( // IF( c.category_id <> 0,ccat.id, // IF( COUNT(se.id) > 0 && COUNT(s.id) < 1, // '1', // IF( COUNT(se.id) < 1 && COUNT(s.id) > 0, // '2', // IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, // '3', // IF( COUNT(v.id) > 1 && COUNT(v.id) < 4, // '4', // IF( COUNT(v.id) > 3, // '5','0'/VVIP/ // ) // ) // ) // ) // ) // ) // ) as customer_category, // IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) \|\| (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) , // '1','2' // ) // as isActive"; // $fields = " v.brand_id, v.model_id, se.ro_number,se.ro_total, se.plate_number, se.service_advisor, se.id, v.conduction_sticker, // concat(e.first_name,' ',e.last_name) as upload_by,se.date_uploaded,sdms.name as dms_name,sd.code dealer_name, sc.code as company_name,sdms.name as dms_name,se.serial_number,se.mileage,se.customer_discount,se.customer_paid_amount, // sb.name as brand, scm.name as model, se.start_date_of_service, // IF( // c.type = 1, concat(c.first_name,' ',c.middle_name,' ',c.last_name),c.corporation_name // ) as name, // ( // IF( c.category_id <> 0,ccat.id, // IF( COUNT(se.id) > 0 && COUNT(s.id) < 1, // '1', // IF( COUNT(se.id) < 1 && COUNT(s.id) > 0, // '2', // IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, // '3', // IF( COUNT(v.id) > 1 && COUNT(v.id) < 4, // '4', // IF( COUNT(v.id) > 3, // '5','0'/VVIP/ // ) // ) // ) // ) // ) // ) // ) as customer_category, // IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) \|\| (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) , // '1','2' // ) // as isActive"; // // echo (sprintf($query, $fields,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)); exit; // // echo (sprintf($query, $fields,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus));exit; // $data_list = $db -> sql_query(sprintf($query, $fields,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)); // $query_count = $db -> select("SELECT COUNT(1) FROM ( ".sprintf($query, $count,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)." ) as total_count"); // // $get_brand_model = "SELECT sb.name as brand, scm.name as model FROM `vehicle` v // // INNER JOIN source_brand sb // // ON v.`brand_id` = sb.id // // INNER JOIN source_car_model scm // // ON v.`model_id` = scm.id // // WHERE 1 "; // $filename = 'request/data/'.strtr($db -> cipher($control_number,true), "+/", "-_" ).'.'.'php'; // $fp = fopen($filename, 'w'); // $col_header = array('No.','Date','Customer','RO Number','RO Total','Brand','Model', 'CS','Plate','Dealer'); // $string = implode(';',array_map('replaceExistingSemiColon',$col_header)); // fwrite($fp,$string.PHP_EOL); // $record_counter = 0; // if( $query_count > 0){ // $record_counter++; // foreach ($data_list as $data){ // // $return_data = array_merge($return_data,array(array('No.'=>$record_counter++,'RO Number'=>$data['ro_number'],'Plate Number'=>$data['plate_number'],'Conduction Sticker'=>$data['conduction_sticker'],'Service Advisor'=>$data['service_advisor'],'Customer Name'=>$data['name']))); // // $return_data = array_merge($return_data,array(array( // // 'No.'=>$record_counter++, // // 'Customer'=>$data['name'], // // 'RO'=>$data['ro_number'], // // 'Brand'=> (intval($data['brand_id']) <> 0) ? $data['brand_name'] : $car['brand'], // // 'Model'=> (intval($data['model_id']) <> 0) ? $data['model_name'] : $car['model'], // // 'Plate'=>$data['plate_number'], // // 'CS'=>$data['conduction_sticker'], // // // 'Serial Number'=>$data['serial_number'], // // 'Service Advisor'=>$data['service_advisor'], // // // 'Mileage'=>$data['mileage'], // // 'Paid Amount'=>($data['customer_paid_amount'] <> '' ) ? $fmt->formatCurrency($data['customer_paid_amount'], "Php") : "", // // 'Discount'=> ($data['customer_discount'] <> '' ) ? $fmt->formatCurrency($data['customer_discount'], "Php") : "", // // 'DMS'=>$data['dms_name'], // // 'Company'=>$data['company_name'], // // 'Dealer'=>$data['dealer_name'], // // 'Date'=>$data['start_date_of_service'], // // // 'Upload Date'=>$data['date_uploaded'], // // // 'Upload By'=>$data['upload_by'], // // ))); // $data_to_insert = array( // $record_counter++, // $data['start_date_of_service'], // $data['name'], // $data['ro_number'], // ($data['ro_total'] <> '' ) ? number_format($data['ro_total'],2) : "", // $data['brand'], // $data['model'], // $data['conduction_sticker'], // $data['plate_number'], // $data['dealer_name'], // ); // $string = implode(';',array_map('replaceExistingSemiColon',$data_to_insert)); // fwrite($fp,$string.PHP_EOL); // } // fclose($fp); // }else{ // // $return_data = array_merge($return_data,array(array('RO Number'=>'','Plate Number'=>'','Conduction Sticker'=>'','Service Advisor'=>'','Customer Name'=>''))); // } // $db -> sql_query("UPDATE request SET record_count = '".(max(0,($record_counter-1)))."' WHERE control_number = '$control_number' "); // return max(0,($record_counter-1));//array($return_data,array($record_counter)); // } //////////////////////////////////////////////////////////////////////// // function getFilteredSalesData($company_list,$insurance_company_id,$insurance_type_id,$finance_company_id,$control_number,$customer_category,$customer_status,$fmt,$filter_date_of_birth,$order_by,$sort_by,$brand_id,$model_id,$customer_type,$customer_data_type,$upload_by_id,$state_id,$city_id,$gender_id,$age_from,$age_to,$dms_id,$company_id,$dealer_id,$activity_date_from,$activity_date_to,$upload_date_from,$upload_date_to,$db){ // if(isNotEmpty($upload_date_from) && isNotEmpty($upload_date_to)){ // $filter_date_upload = " AND s.date_uploaded BETWEEN DATE_FORMAT('$upload_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$upload_date_to', '%Y-%m-%d 23:59:00') "; // }else{ // $filter_date_upload = ""; // } // if(isNotEmpty($activity_date_from) && isNotEmpty($activity_date_to)){ // $filter_date_created = " AND s.activity_date BETWEEN DATE_FORMAT('$activity_date_from', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$activity_date_to', '%Y-%m-%d 23:59:00') "; // }else{ // $filter_date_created = ""; // } // if(isNotEmpty($age_from) && isNotEmpty($age_to)){ // $filter_age = " AND TIMESTAMPDIFF(YEAR,c.date_of_birth, CURDATE()) BETWEEN '$age_from' AND '$age_to' "; // }else{ // $filter_age = ""; // } // if(isNotEmpty($upload_by_id)){ // $filter_upload_by = " AND s.upload_by = '$upload_by_id' "; // }else{ // $filter_upload_by = ""; // } // $dms = ($dms_id != '0') ? " AND s.dms_id = '$dms_id' " : "" ; // $company = ($company_id != '0') ? " AND scd.company_id = '$company_id' " : " AND scd.company_id IN (".$company_list.") " ; // $dealer = ($dealer_id != '0') ? " AND scd.dealer_id = '$dealer_id' " : "" ; // $state = ($state_id != '0') ? " AND cc.address_1_state = '$state_id' " : "" ; // $city = ($city_id != '0') ? " AND cc.address_1_city = '$city_id' " : "" ; // $gender = ($gender_id != '0') ? " AND c.gender_id = '$gender_id' " : "" ; // $customertype = ($customer_type != '0') ? " AND c.type = '$customer_type' " : "" ; // $brand = ($brand_id != '0') ? " AND sb.id = '$brand_id' " : "" ; // $model = ($model_id != '0') ? " AND scm.id = '$model_id' " : "" ; // $insurance_company = ($insurance_company_id != '0') ? " AND ( s.insurance_company_id = '$insurance_company_id') " : "" ; // $insurance_type = ($insurance_type_id != '0') ? " AND ( s.insurance_type_id = '$insurance_type_id') " : "" ; // $finance_company = ($finance_company_id != '0') ? " AND ( s.finance_company_id = '$finance_company_id') " : "" ; // $complete_data = ""; // $incomplete_data = ""; // $address_1 = ""; // $date_of_birth =""; // if(isNotEmpty($customer_data_type) && $customer_data_type != '0'){ // switch($customer_data_type){ // case "Complete Data": // $complete_data = " AND ((c.date_of_birth <> '' and ag.id <> 0 and cc.address_1 <> '' and cc.email_1 <> '' and cc.mobile_phone_1 <> '') AND c.type = 1) // OR // (cc.address_1 <> '' AND cc.email_1 <> '' AND c.type = 2) "; // break; // case "Incomplete Data": // $incomplete_data = " AND ((c.date_of_birth = '' OR // ag.id = 0 OR // cc.address_1 = '' OR cc.email_1 = '') AND c.type = 1) OR (cc.address_1 = '' or cc.email_1 = '' AND c.type = 2) "; // break; // case "No Mobile": // $mobile_phone_1 = " AND cc.mobile_phone_1 = '' "; // break; // case "No Email": // $email_1 = " AND cc.email_1 = '' "; // break; // case "No Address": // $address_1 = " AND cc.address_1 = '' "; // break; // case "No Gender": // $gender = " AND ag.gender_id = '0' AND c.type = '1' "; // break; // case "No Date of birth": // $date_of_birth = " AND c.date_of_birth = '' AND c.type = '1' "; // break; // } // } // if(isNotEmpty($filter_date_of_birth) && isNotEmpty($filter_date_of_birth)){ // $filter_dob = " AND c.date_of_birth = '$filter_date_of_birth' "; // }else{ // $filter_dob = ""; // } // switch(intval($sort_by)){ // case 1: //lastname // $filter_sort_by = " c.last_name "; // break; // case 2: //firstname // $filter_sort_by = " c.first_name "; // break; // case 3: //corporation name // $filter_sort_by = " c.corporation_name "; // break; // case 4: //date created // $filter_sort_by = " s.activity_date "; // break; // case 5: //date uploaded // $filter_sort_by = " s.date_uploaded "; // break; // } // $customercategory = ($customer_category != '0') ? " HAVING customer_category = '$customer_category' " : "" ; // $customerstatus = ($customer_status != '0') ? " ".(($customer_category != '0') ? " AND " : " HAVING " )." isActive = '$customer_status' " : "" ; // $return_data = array(); // $query = "SELECT %s FROM sales s // INNER JOIN vehicle v ON s.vehicle_id = v.id // INNER JOIN service se ON se.vehicle_id = v.id // INNER JOIN source_brand sb ON v.brand_id=sb.id // INNER JOIN source_car_model scm ON v.model_id=scm.id // INNER JOIN source_company_dealer scd ON scd.id=s.company_dealer_id // INNER JOIN source_company sc ON sc.id = scd.company_id // INNER JOIN source_dealer sd ON sd.id= scd.dealer_id // INNER JOIN employee e ON s.upload_by = e.id // INNER JOIN source_dms sdms ON s.dms_id = sdms.id // LEFT OUTER JOIN customer c ON v.customer_record_id = c.id // LEFT OUTER JOIN customer_contact cc ON c.id = cc.customer_id // LEFT OUTER JOIN customer_category ccat ON c.category_id = ccat.id // LEFT OUTER JOIN sales_consultant sct ON s.sales_person = sct.id // WHERE 1 // %s / brand / // %s / model / // %s / customer type / // %s / upload by / // %s / state / // %s / city / // %s / gender / // %s / filter age / // %s / dms type / // %s / company / // %s / dealer / // %s / filter_date_upload / // %s / filter_date_created / // %s / filter_dob / // %s / insurance_company / // %s / insurance_type / // %s / finance_company / // AND s.status = 1 GROUP BY s.id %s %s ORDER BY $filter_sort_by $order_by"; // $count = " ( // IF( c.category_id <> 0,ccat.id, // IF( COUNT(se.id) > 0 && COUNT(s.id) < 1, // '1', // IF( COUNT(se.id) < 1 && COUNT(s.id) > 0, // '2', // IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, // '3', // IF( COUNT(v.id) > 1 && COUNT(v.id) < 4, // '4', // IF( COUNT(v.id) > 3, // '5','0'/VVIP/ // ) // ) // ) // ) // ) // ) // ) as customer_category, // IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) \|\| (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) , // '1','2' // ) // as isActive"; // $fields = " v.brand_id,v.model_id, sb.name as brand, scm.name as model,s.id, s.order_number, s.po_number, s.activity_date, s.customer_id,s.sales_cost,s.sale_price,s.sale_discount,s.claim_amount,s.floor_plan_amount,v.conduction_sticker,v.plate_number,s.serial_number,s.engine_number,s.claim_number,s.deal_number,s.deal_date,s.stock_number, // s.warehouse,s.age_of_unit,sdms.name as dms_name,sc.code as company_name,sd.code as dealer_name, s.date_uploaded,concat(e.first_name,' ',e.last_name) as upload_by,CONCAT(sct.first_name, ' ', sct.last_name) as sales_person, // IF( // c.type = 1, concat(c.first_name,' ',c.middle_name,' ',c.last_name),c.corporation_name // ) as name ,( // IF( c.category_id <> 0,ccat.id, // IF( COUNT(se.id) > 0 && COUNT(s.id) < 1, // '1', // IF( COUNT(se.id) < 1 && COUNT(s.id) > 0, // '2', // IF( COUNT(v.id) = 1 && COUNT(se.id) > 0 && COUNT(s.id) > 0, // '3', // IF( COUNT(v.id) > 1 && COUNT(v.id) < 4, // '4', // IF( COUNT(v.id) > 3, // '5','0'/VVIP/ // ) // ) // ) // ) // ) // ) // ) as customer_category, // IF( (SELECT DATEDIFF(NOW(),se.start_date_of_service) <= 365) \|\| (SELECT DATEDIFF(NOW(),s.activity_date) <= 365) , // '1','2' // ) // as isActive"; // // echo (sprintf($query, $fields,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)); exit; // echo sprintf($query, $fields,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus); exit; // $data_list = $db -> sql_query(sprintf($query, $fields,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)); // $query_count = $db -> select("SELECT COUNT() FROM ( ".sprintf($query, $count,$brand,$model,$customertype,$filter_upload_by,$state, $city,$gender,$filter_age,$dms,$company,$dealer,$filter_date_upload,$filter_date_created,$filter_dob,$insurance_company,$insurance_type,$finance_company,$customercategory,$customerstatus)." ) as total_count"); // $filename = 'request/data/'.strtr($db -> cipher($control_number,true), "+/", "-_" ).'.'.'php'; // $fp = fopen($filename, 'w'); // $col_header = array('No.','Date','Customer','Order #','Price','Discount','Brand','Model', 'CS','Dealer'); // $string = implode(';',array_map('replaceExistingSemiColon',$col_header)); // fwrite($fp,$string.PHP_EOL); // $record_counter = 0; // if( $query_count > 0){ // $record_counter++; // foreach ($data_list as $data){ // // $return_data = array_merge($return_data,array(array('No.'=>$record_counter++,'Order Number'=>$data['order_number'],'PO Number'=>$data['po_number'],'Date'=>$data['activity_date'],'Sales Person'=>$data['sales_person'],'Customer Name'=>$data['name']))); // // $return_data = array_merge($return_data,array(array( // // 'No.'=>$record_counter++, // // 'Customer'=>$data['name'], // // 'Order #'=>$data['order_number'], // // 'Brand'=> (intval($data['brand_id']) <> 0) ? $data['brand'] : $car['brand'], // // 'Model'=> (intval($data['model_id']) <> 0) ? $data['model'] : $car['model'], // // // 'PO #'=>$data['po_number'], // // 'Cost'=>($data['sales_cost'] <> '' ) ? $fmt->formatCurrency($data['sales_cost'], "Php") : "", // // 'Price'=>($data['sale_price'] <> '' ) ? $fmt->formatCurrency($data['sale_price'], "Php") : "", // // 'Discount'=>($data['sale_discount'] <> '' ) ? $fmt->formatCurrency($data['sale_discount'], "Php") : "", // // // 'Claim Amount'=>$data['claim_amount'], // // // 'Floor Plan Amount'=>$data['floor_plan_amount'], // // 'CS'=>$data['conduction_sticker'], // // // 'Serial Number'=>$data['serial_number'], // // // 'Engine Number'=>$data['engine_number'], // // // 'Claim Number'=>$data['claim_number'], // // // 'Deal Number'=>$data['deal_number'], // // // 'Deal Date'=>$data['deal_date'], // // // 'Stock Number'=>$data['stock_number'], // // // 'Warehouse'=>$data['warehouse'], // // // 'Age of Unit'=>$data['age_of_unit'], // // 'Sales Person'=>$data['sales_person'], // // 'DMS'=>$data['dms_name'], // // 'Company'=>$data['company_name'], // // 'Dealer'=>$data['dealer_name'], // // 'Date'=>$data['activity_date'], // // // 'Upload Date'=>$data['date_uploaded'], // // // 'Upload By'=>$data['upload_by'], // // ))); // $data_to_insert = array( // $record_counter++, // $data['activity_date'], // $data['name'], // $data['order_number'], // ($data['sale_price'] <> '' ) ? number_format($data['sale_price'],2) : "", // ($data['sale_discount'] <> '' ) ? number_format($data['sale_discount'],2) : "", // $data['brand'], // $data['model'], // $data['conduction_sticker'], // $data['dealer_name'], // ); // $string = implode(';',array_map('replaceExistingSemiColon',$data_to_insert)); // fwrite($fp,$string.PHP_EOL); // } // fclose($fp); // }else{ // // $return_data = array_merge($return_data,array(array('RO Number'=>'','Plate Number'=>'','Conduction Sticker'=>'','Service Advisor'=>'','Customer Name'=>''))); // } // $db -> sql_query("UPDATE text_blast SET record_count = '".(max(0,($record_counter-1)))."' WHERE control_number = '$control_number' "); // return max(0,($record_counter-1));//array($return_data,array($record_counter)); // } function fix_date($data, $format){ if(!isNotEmpty($data)){ return ""; } // 01/23/2020 false // 23/01/2020 true $array_date = explode("/",explode(" ",$data)[0]); if($format){ //if format == true swap index 1 and 2 $return_date = $array_date[2].'-'.$array_date[1].'-'.$array_date[0]; return $return_date; }else{ $return_date = $array_date[2].'-'.$array_date[0].'-'.$array_date[1]; return $return_date; } } function isNotEmpty($data){ return preg_match('/\S/', $data); } function cleanStr($string) { $string = str_replace(' ', '-', $string); // Replaces all spaces with hyphens. return preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars. } function mask($str, $first, $last) { $str = cleanStr($str); $len = strlen($str); $toShow = $first + $last; return substr($str, 0, $len <= $toShow ? 0 : $first).str_repeat("*", $len - ($len <= $toShow ? 0 : $toShow)).substr($str, $len - $last, $len <= $toShow ? 0 : $last); } function replaceExistingSemiColon($s){ return str_replace(';',' ',$s); } // exit; ?>

| ver. 1.4 | . | PHP 8.1.32 | Generation time: 0 | proxy | phpinfo | Settings